package com.hangxintong.common.base.entity;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public interface OrderRepositry extends JpaRepository<LmOrder, String>, JpaSpecificationExecutor<LmOrder> {
    // 我的所有订单（带状态查询）
    List<LmOrder> findByStatusAndCreateId(Integer status, long id);

    // 我的所有订单
    List<LmOrder> findByCreateId(long id);

    LmOrder findByOrderNum(String trade_status);

    List<LmOrder> findByGoodsIdAndGoodsCateAndCreateIdAndStatus(String goodsId, int goodsCate, Integer userId, int status);

    @Query(value = "SELECT id from LmOrder where companyId=?1 and companyType=?2")
    List<Long> findOrderList(Long assocId, Integer ctype);

    @Query("select COALESCE(SUM(realTotalMoney),0) from LmOrder where id in(?1)")
    String total(String total);

    @Query("select COALESCE(SUM(realTotalMoney),0) from LmOrder where createId=?1 and status=2")
    String totalUserAll(Integer uid);

    @Query("select COALESCE(SUM(realTotalMoney),0) from LmOrder where payCompanyId=?1 and payCompanyType=?2 and status=2")
    String totalCompanyAll(Integer cid, Integer ctype);


    LmOrder findFirstByCreateIdAndGoodsIdAndGoodsCateAndStatus(Integer uid, String id, int cate, int i);

    @Query("select COALESCE(SUM(realTotalMoney),0) from LmOrder where goodsId=?1 and goodsCate=?2 and status=2")
    String salesAmount(String id, int i);

    List<LmOrder> findByIdIn(List<String> orderIdList);


    LmOrder findFirstByCreateIdAndGoodsIdInAndGoodsCate(Integer id, List<String> pid, int i);

    LmOrder findFirstByCreateIdAndGoodsIdInAndGoodsCateAndStatusIn(Integer id, List<String> pid, int i, int[] ss);

    /**
     * 查询我购买的商家集合
     *
     * @param rr 订单id集合
     * @param id 买家id
     * @return
     */
    @Query(value = "SELECT * FROM `lm_order` where create_id=?2 and company_type=4 and id in(?1) GROUP BY company_id", nativeQuery = true)
    List<String> findByCidIn(List<String> rr, Integer id);

    LmOrder findFirstByCreateIdAndGoodsIdAndGoodsCate(Integer id, String id1, int i);

    @Query(value = "SELECT company_name companyName,COALESCE(SUM(pay_money),0) allMoney,COALESCE(sum(number),0) allNumber ,?2 sourceType,company_id FROM `lm_order` where goods_id in(?1) and status=2 and is_delete=0 ", nativeQuery = true)
    Map<String, Object> countPayMoney(List<String> itemArr, Integer type);

    @Query(value = "SELECT company_name companyName,COALESCE(SUM(pay_money),0) allMoney,COALESCE(sum(number),0) allNumber ,?4 sourceType,company_id  FROM `lm_order` where goods_id in(?1) and status=2 and pay_time>=?2 and pay_time<=?3 and is_delete=0 ", nativeQuery = true)
    Map<String, Object> countPayMoneyAndXid(List<String> itemArr, String startTime, String endTime, Integer type);

    @Query(value = "SELECT company_name companyName,COALESCE(SUM(pay_money),0) allMoney,COALESCE(sum(number),0) allNumber ,?3 sourceType,company_id  FROM `lm_order` where goods_id in(?1) and company_id=?2 and status=2 and is_delete=0 ", nativeQuery = true)
    Map<String, Object> countUnAuthPayMoneyAndXid(List<String> itemArr, Integer searchXid, Integer type);

    @Query(value = "SELECT company_name companyName,COALESCE(SUM(pay_money),0) allMoney,COALESCE(sum(number),0) allNumber ,?4 sourceType,company_id  FROM `lm_order` where goods_id in(?1) and status=2 and pay_time>=?2 and pay_time<=?3 and is_delete=0", nativeQuery = true)
    Map<String, Object> countUnAuthPayMoneyAndTime(List<String> itemArr, String startTime, String endTime, Integer type);

    @Query(value = "SELECT company_name companyName,COALESCE(SUM(pay_money),0) allMoney,COALESCE(sum(number),0) allNumber ,?5 sourceType,company_id  FROM `lm_order` where goods_id in(?1) and company_id=?2 and status=2 and pay_time>=?3 and pay_time<=?4 and is_delete=0", nativeQuery = true)
    Map<String, Object> countUnAuthPayMoneyAndXidTime(List<String> itemArr, Integer searchXid, String startTime, String endTime, Integer type);

    /**
     * 根据发票申请id查询当前协会所有订单
     *
     * @param i
     * @param xid
     * @param invoiceLogIdList
     * @return
     */
    List<LmOrder> findAllByCompanyTypeAndCompanyIdAndIsInvoiceIdIn(Long i, Long xid, List<String> invoiceLogIdList);

    List<LmOrder> findAllByCompanyTypeAndCompanyId(Long i, Long cid);

    LmOrder findFirstByCreateIdAndGoodsIdAndGoodsCateAndStatusIn(Integer id, String id1, int i, Integer[] s);


    List<LmOrder> findByGoodsIdAndGoodsCate(String itemId, int i);

    List<LmOrder> findAllByCreateIdAndStatusAndGoodsCate(Integer id, int i, int i1);

    /**
     * @param id
     * @return
     */
    @Query(value = "SELECT * FROM `lm_order` where create_id=?1", nativeQuery = true)
    List<LmOrder> queryByCreateId(Integer id);

    LmOrder findByIdAndCompanyTypeAndCompanyIdAndCreateId(String orderId, Long i, Long xid, Integer userId);

    /**
     * 查询订单的存在时间
     *
     * @param orderId
     * @return
     */
    @Query(value = "select DATEDIFF(now(),create_time) as isDate from lm_order where id = ?1", nativeQuery = true)
    Map orderDate(String orderId);

    LmOrder findByIdAndCreateId(String orderId, Integer userId);

    List<LmOrder> findAllByCompanyTypeAndCompanyIdAndCreateId(long l, Long valueOf, Integer id);

    List<LmOrder> findAllByCompanyTypeAndCompanyIdAndCreateIdAndStatus(long l, Long valueOf, Integer id, int i);

    @Query("select COALESCE(SUM(realTotalMoney),0) from LmOrder where goodsId in(?1) and payTime >=?2 and payTime <=?3 and status=2 and isDelete=0")
    String getOrderTotal(List<String> asList, String sTime, String eTime);

    @Query("select count(id) from LmOrder where goodsId in(?1) and payTime >=?2 and payTime <=?3 and status=2 and isDelete=0")
    int getOrderNum(List<String> asList, String sTime, String eTime);

    @Query(value = "select goods_id,goods_title,COALESCE(SUM(number),0) allNumber, COALESCE(SUM(pay_money),0) allMoney,COALESCE(SUM(pay_money),0)*?4 resourceMoney,COALESCE(SUM(pay_money),0)*?5 saleMoney from lm_order where goods_id in (?1)" +
            " and pay_time >=?2 and pay_time <= ?3 and status=2 and is_delete=0 GROUP BY goods_id", countQuery = "select id from lm_order where goods_id in (?1) and pay_time >=?2 and pay_time <= ?3 and status=2 and is_delete=0 GROUP BY goods_id", nativeQuery = true)
    Page<Map<String, Object>> getItemOrderTable(List<String> listItem, String starTime, String endTime, double resourceSite, double saleSite, Pageable page);

    @Query(value = "select goods_id,goods_title,COALESCE(SUM(number),0) allNumber, COALESCE(SUM(pay_money),0) allMoney from lm_order where goods_id in (?1)" +
            " and pay_time >=?2 and pay_time <= ?3 and status=2 and is_delete=0 GROUP BY goods_id", countQuery = "select id from lm_order where goods_id in (?1) and pay_time >=?2 and pay_time <= ?3 and status=2 and is_delete=0 GROUP BY goods_id", nativeQuery = true)
    Page<Map<String, Object>> getItemOrderTableZy(List<String> listItem, String starTime, String endTime, Pageable page);

    @Query(value = "select goods_id,goods_title,COALESCE(SUM(number),0) allNumber, COALESCE(SUM(pay_money),0) allMoney from lm_order where goods_id in (?1)" +
            " and pay_time >=?2 and pay_time <= ?3 and status=2 and is_delete=0", nativeQuery = true)
    Map<String, Object> countOrderInfo(List<String> listItem, String starTime, String endTime);

    /**
     * 查询已付款的培训订单
     *
     * @param i
     * @param i1
     * @return
     */
    List<LmOrder> findByStatusAndGoodsCate(int i, int i1);

    List<LmOrder> findByGoodsId(String id);

    /**
     * 查询人员购买的商品ids
     *
     * @param id
     * @return
     */
    @Query(value = "select goods_id from lm_order where create_id = ?1 and status=2", nativeQuery = true)
    List<String> getUserItemIds(Integer id);

    /**
     * 查询某人购买数据
     *
     * @author Mr.zhang
     * @date 2020/11/27 14:38
     */
    LmOrder findFirstByGoodsIdAndCreateIdAndStatus(String id, Integer value, int i);

    /**
     * 通过商品id和类型查找单个订单
     *
     * @param goodsId
     * @param i
     * @return
     * @author mxf 2021-01-05
     */
    LmOrder findFirstByGoodsIdAndGoodsCate(String goodsId, int i);

    /**
     * 查询出大于两小时未支付的会费订单
     *
     * @return
     */
    @Query(value = "SELECT *  FROM lm_order WHERE goods_cate=3 and `status` NOT IN(2,6) and TIMESTAMPDIFF(HOUR,create_time,?1) >= 2", nativeQuery = true)
    List<LmOrder> queryByStatusAndGoodsCateAndLgt2Hours(Date time);

    /**
     * 通过支付宝的单号查询订单详情
     *
     * @param payWayNum
     * @param status
     * @author mxf 2021-01-14
     */
    LmOrder findFirstByPayWayNumAndStatus(String payWayNum, int status);

}
